package com.jiang.sevice;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.HashSet;
import java.util.Set;

import com.jiang.vo.Member;

public class MemberLoginService {

	private static final String driver = "com.mysql.jdbc.Driver";
	private static final String url = "jdbc:mysql://localhost:3306/shirodb?useSSL=false&&characterEncoding=utf-8";
	private static final String username = "root";
	private static final String password = "123456";
	private Connection connection;

	public MemberLoginService() {
		this.buildConn();
	}

	// 建立连接
	public void buildConn() {

		try {
			Class.forName(driver);
			this.connection = DriverManager.getConnection(url, username, password);
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		} catch (SQLException e) {
			e.printStackTrace();
		}

	}

	/**
	 * 关闭连接
	 */
	public void close() {

		if (connection != null) {
			try {
				connection.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}

	}

	// 通过mid获取用户实体
	public Member getMember(String mid) {

		String sql = "SELECT mid,password,name,locked FROM member m WHERE m.mid=? ";
		Member m = null;
		try {
			PreparedStatement ps = this.connection.prepareStatement(sql);
			ps.setString(1, mid);
			ResultSet rs = ps.executeQuery();
			if (rs.next()) {
				// 直接获取数据返回
				m = new Member();
				m.setMid(rs.getString("mid"));
				m.setPassword(rs.getString("password"));
				m.setName(rs.getString("name"));
				m.setLocked(rs.getInt("locked"));
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return m;
	}

	// 根据用户名获取角色
	public Set<String> getRolesByMid(String mid) {

		Set<String> roles = null;
		String sql = "SELECT r.flag FROM role r INNER JOIN  (SELECT mr.rid,mr.mid FROM member_role mr WHERE mr.mid=? ) b ON r.rid=b.rid";
		PreparedStatement ps;
		try {
			ps = this.connection.prepareStatement(sql);
			ps.setString(1, mid);
			ResultSet rs = ps.executeQuery();
			if (rs.next()) {
				roles = new HashSet<String>();
				rs.beforeFirst();
			}
			while (rs.next()) {
				roles.add(rs.getString(1));
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}

		return roles;
	}

	// 根据用户名获取权限
	public Set<String> getActionsByMid(String mid) {

		Set<String> actions = null;
		String sql = "SELECT flag FROM ACTION  a WHERE a.actid IN (SELECT actid FROM role_action WHERE  rid IN(SELECT mr.rid FROM member_role mr WHERE mr.mid=?))";
		PreparedStatement ps;
		try {
			ps = this.connection.prepareStatement(sql);
			ps.setString(1, mid);
			ResultSet rs = ps.executeQuery();
			if (rs.next()) {
				actions = new HashSet<String>();
				rs.beforeFirst();
			}
			while (rs.next()) {
				actions.add(rs.getString(1));
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return actions;
	}

}
